﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.IO;
using System.Diagnostics;
using System.Text.RegularExpressions;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Configuration;


namespace PATBController
{

    public class ATBSchemaDB : IDisposable
    {
        private SqlConnection g_sqlConn;
        private bool _disposed;

        public ATBSchemaDB()
        {
            string sConnstring = ConfigurationManager.ConnectionStrings["NSMATBSQLServer"].ConnectionString;
            

            g_sqlConn = new
                SqlConnection(sConnstring);
            g_sqlConn.Open();
            _disposed = false;
        }

        public DataTable GetDefTestMachineList()
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.ServiceGetTestMachineList", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;

        }
        public DataTable GetRunsList()
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.ServiceGetRunsList", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }
        public void StartRun(int iRunID)
        {

            using (SqlCommand cmd = new SqlCommand(@"dbo.ServiceStartRun", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@RunsID", SqlDbType.Int);
                param.Value = iRunID;
                cmd.Parameters.Add(param);

                cmd.ExecuteScalar();
            }
            return;
        }
        public DataTable GetTestListForRun(int iRunsID)
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.ServiceGetTestListForRun", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@RunsID", SqlDbType.Int);
                param.Value = iRunsID;
                cmd.Parameters.Add(param);

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }
        public DataTable GetParamListForBaseState(int iResTestSetID, int iDefRunDataID)
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.ServiceGetParamListForBaseState", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@ResTestSetID", SqlDbType.Int);
                param.Value = iResTestSetID;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@DefRunDataID", SqlDbType.Int);
                param.Value = iDefRunDataID;
                cmd.Parameters.Add(param);

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }
        public DataTable GetTestDataSetRow(int pDefTestDataSetDataID)
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.ServiceGetTestDataSetRow", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@DefTestDataSetDataID", SqlDbType.Int);
                param.Value = pDefTestDataSetDataID;
                cmd.Parameters.Add(param);

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }
        public DataTable GetListValuesConcat(string sListName)
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.ServiceGetListValuesConcat", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@ListName", SqlDbType.VarChar);
                param.Value = sListName;
                cmd.Parameters.Add(param);

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }
        public string GetListValueForParam(string sListName)
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.ServiceGetListValueForParam", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@ListName", SqlDbType.VarChar);
                param.Value = sListName;
                cmd.Parameters.Add(param);

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }

            }

            if (dtResults.Rows.Count > 0)
                return dtResults.Rows[0][0].ToString();

            return "List or ListItem not present for list name " + sListName;
        }
        public DataTable GetTestSteps(int iDefTestCaseID)
        {
            DataTable dtResults = new DataTable();

            using (SqlCommand cmd = new SqlCommand(@"dbo.ServiceGetTestSteps", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@DefTestCaseID", SqlDbType.Int);
                param.Value = iDefTestCaseID;
                cmd.Parameters.Add(param);

                using (SqlDataReader objDataReader = cmd.ExecuteReader())
                {
                    dtResults.Load(objDataReader);
                }
            }
            return dtResults;
        }
        public void TestIndicateStart(int iResTestID, int iRunsID)
        {

            using (SqlCommand cmd = new SqlCommand(@"dbo.ServiceStartTest", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@RunsID", SqlDbType.Int);
                param.Value = iRunsID;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@ResTestCaseID", SqlDbType.Int);
                param.Value = iResTestID;
                cmd.Parameters.Add(param);

                cmd.ExecuteScalar();
            }
            return;
        }
        public void TestIndicateEnd(int iResTestID, int iRunsID, string sTestMachineUsed)
        {

            using (SqlCommand cmd = new SqlCommand(@"dbo.ServiceEndTest", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@RunsID", SqlDbType.Int);
                param.Value = iRunsID;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@ResTestCaseID", SqlDbType.Int);
                param.Value = iResTestID;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@TestMachineUsed", SqlDbType.VarChar);
                param.Value = sTestMachineUsed;
                cmd.Parameters.Add(param);

                cmd.ExecuteScalar();
            }
            return;
        }
        public void TestReset(int iResTestID, int iRunsID)
        {

            using (SqlCommand cmd = new SqlCommand(@"dbo.ServiceResetTest", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@RunsID", SqlDbType.Int);
                param.Value = iRunsID;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@ResTestCaseID", SqlDbType.Int);
                param.Value = iResTestID;
                cmd.Parameters.Add(param);

                cmd.ExecuteScalar();
            }
            return;
        }
        public void TestPostResultError(int iResTestID, int iRunsID, string sErrorMessage)
        {

            using (SqlCommand cmd = new SqlCommand(@"dbo.ServiceSetTestError", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@RunsID", SqlDbType.Int);
                param.Value = iRunsID;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@ResTestCaseID", SqlDbType.Int);
                param.Value = iResTestID;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@ErrorMessage", SqlDbType.VarChar);
                param.Value = sErrorMessage;
                cmd.Parameters.Add(param);

                cmd.ExecuteScalar();
            }
            return;
        }
        public int TestPostResultStep(
            int iResTestCaseID, 
            int iDefTestStepID,
            string sStepName,
            string sActionWordName,
            string sResultText,
            string sResult,
            int iRunTimeSecs,
            bool bEvidence
            )
        {
            int iOutIdentity = 0;

            using (SqlCommand cmd = new SqlCommand(@"dbo.ServiceTestStepResult", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@ResTestCaseID", SqlDbType.Int);
                param.Value = iResTestCaseID;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@DefTestStepID", SqlDbType.Int);
                param.Value = iDefTestStepID;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@StepName", SqlDbType.VarChar);
                param.Value = sStepName;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@ActionWordName", SqlDbType.VarChar);
                param.Value = sActionWordName;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@ResultText", SqlDbType.VarChar);
                param.Value = sResultText;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@Result", SqlDbType.VarChar);
                param.Value = sResult;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@RunTimeSecs", SqlDbType.Int);
                param.Value = iRunTimeSecs;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@Evidence", SqlDbType.Bit);
                param.Value = bEvidence;
                cmd.Parameters.Add(param);

                SqlParameter paramOutIdentity = new SqlParameter("@OutIdentity", SqlDbType.Int);
                paramOutIdentity.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(paramOutIdentity);

                cmd.ExecuteNonQuery();

                iOutIdentity = Convert.ToInt32(cmd.Parameters["@OutIdentity"].Value);
            }
            return iOutIdentity;
        }
        public void TestPostResultStepParam(
            int iResTestStepID,
            int iSeqNo,
            string sName,
            string sValue
            )
        {

            using (SqlCommand cmd = new SqlCommand(@"dbo.ServiceTestStepResultParam", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@ResTestStepID", SqlDbType.Int);
                param.Value = iResTestStepID;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@SeqNo", SqlDbType.Int);
                param.Value = iSeqNo;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@Name", SqlDbType.VarChar);
                param.Value = sName;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@Value", SqlDbType.VarChar);
                param.Value = sValue;
                cmd.Parameters.Add(param);

                cmd.ExecuteScalar();
            }
            return;
        }
        public void TestPostResultEvidence
            (
	            int iSourceID,
	            string sSourceTable,
	            string sName,
	            string sDataType,
                string sFileName,
	            string sDescription,
	            string sDataFileSpec
            )
        {

            using (SqlCommand cmd = new SqlCommand(@"dbo.ServiceTestResultEvidence", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;


                param = new SqlParameter("@SourceID", SqlDbType.Int);
                param.Value = iSourceID;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@SourceTable", SqlDbType.VarChar);
                param.Value = sSourceTable;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@Name", SqlDbType.VarChar);
                param.Value = sName;
                cmd.Parameters.Add(param);
                
                param = new SqlParameter("@DataType", SqlDbType.VarChar);
                param.Value = sDataType;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@FileName", SqlDbType.VarChar);
                param.Value = sFileName;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@Description", SqlDbType.VarChar);
                param.Value = sDescription;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@Data", SqlDbType.VarBinary);
                param.Value = System.IO.File.ReadAllBytes(sDataFileSpec);
                cmd.Parameters.Add(param);

                cmd.ExecuteScalar();
            }
            return;
        }
        public void TestPostResultComplete(int iResTestID, int iRunsID, string sResult, bool bEvidence)
        {

            using (SqlCommand cmd = new SqlCommand(@"dbo.ServiceSetTestComplete", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@RunsID", SqlDbType.Int);
                param.Value = iRunsID;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@ResTestCaseID", SqlDbType.Int);
                param.Value = iResTestID;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@Result", SqlDbType.VarChar);
                param.Value = sResult;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@Evidence", SqlDbType.Bit);
                param.Value = bEvidence;
                cmd.Parameters.Add(param);

                cmd.ExecuteScalar();
            }
            return;
        }

        public void CheckRunsComplete(int iRunsID)
        {

            using (SqlCommand cmd = new SqlCommand(@"dbo.ServiceCheckRunsComplete", g_sqlConn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter param;

                param = new SqlParameter("@RunsID", SqlDbType.Int);
                param.Value = iRunsID;
                cmd.Parameters.Add(param);

                cmd.ExecuteScalar();
            }
            return;
        }



        public void Dispose()
        {
            Dispose(true);

            // Call SupressFinalize in case a subclass implements a finalizer.
            GC.SuppressFinalize(this);
        }
        protected virtual void Dispose(bool disposing)
        {
            // If you need thread safety, use a lock around these  
            // operations, as well as in your methods that use the resource. 
            if (!_disposed)
            {
                if (disposing)
                {
                    g_sqlConn.Close();
                    g_sqlConn.Dispose();
                }

                // Indicate that the instance has been disposed.
                _disposed = true;
            }
        }

    }

   

}